package com.darren.euc.dao.impl;


import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.jdbc.Work;
import org.springframework.stereotype.Repository;

import com.darren.euc.dao.BillDao;
import com.darren.euc.entity.Bill;


@Repository
public class BillDaoImpl extends BaseDaoImpl<Bill, Integer> implements BillDao {
	
	@SuppressWarnings("unchecked")
	@Override
	public List<Bill> getAll() {
		Session session = sessionFactory.getCurrentSession();	
		List<Bill> bills = session.createQuery("from Bill ").list();
		return bills;
	}
	
	
	@Override
	public void generateBillData(){
		Session session = sessionFactory.getCurrentSession();	
		session.doWork(new Work() {			
			@Override
			public void execute(Connection connection) throws SQLException {
				// TODO Auto-generated method stub
				CallableStatement  callableStatement = connection.prepareCall("{call usp_generate_bills}");
				callableStatement.execute();
			}
		});
	}
	
	
	@Override
	public void updateBillStatus(){
		Session session = sessionFactory.getCurrentSession();	
		session.doWork(new Work() {			
			@Override
			public void execute(Connection connection) throws SQLException {
				// TODO Auto-generated method stub
				CallableStatement  callableStatement = connection.prepareCall("{call usp_generate_bills}");
				callableStatement.execute();
			}
		});
	}
	
	@Override
	public void updateRoomOrderHirePurchase(Integer billId){
		String sql = "UPDATE euc_contract_hire_purchase AS hirepurchase	"+
				"	INNER JOIN euc_bill_detail AS billdetail ON billdetail.contract_id = hirepurchase.contract_id and billdetail.hire_purchase_number = hirepurchase.hire_purchase_number 	"+
				"	SET hirepurchase.real_payment_amount = billdetail.paid_amount,hirepurchase.hire_purchase_status = IF(billdetail.paid_amount > 0 ,'已回款' ,'未回款') 	" +
				"	WHERE billdetail.bill_id = ?  and order_type = 1  ;";
		Session session = sessionFactory.getCurrentSession();
		Query query  = session.createSQLQuery(sql);
		query.setParameter(0, billId);
		query.executeUpdate();
	}
	
	
	private static String sqlUpdateTempOrderStatus = " update euc_bill_detail AS billDetail  INNER JOIN temporary_order as temporaryorder on temporaryorder.id = billDetail.order_id	"+
									"	SET temporaryorder.orderStatus = IF(billDetail.paid_amount >= billDetail.payable_amount,1,0)	"+
									"	WHERE billDetail.bill_id = ? AND billDetail.order_type = 4;";
	
	private static String sqlUpdateMeetingOrderStatus = " UPDATE euc_meetingroom_order as meetingroomorder 	"+
										"	INNER JOIN euc_member as member on member.id = meetingroomorder.member_id	"+
										"	INNER JOIN (SELECT meetingroomorder.`day`,IF(billDetail.paid_amount >= billDetail.payable_amount,2,1) pay_status,billDetail.client_id	"+
										"							FROM euc_bill_detail AS billDetail	"+
										"							INNER JOIN euc_meetingroom_order as meetingroomorder on billDetail.order_id = meetingroomorder.order_id	"+
										"							WHERE billDetail.bill_id = ? AND billDetail.order_type = 2) TT ON member.client_id = TT.client_id	"+
										"	SET meetingroomorder.pay_status = TT.pay_status	"+
										"	WHERE meetingroomorder.pay_status <> 0 	"+
										"	  AND meetingroomorder.`status` <> 0	"+
										"	  AND meetingroomorder.`day` BETWEEN  date_add(TT.`day`, interval - day(TT.`day`) + 1 day) AND last_day(date_add(TT.`day`, interval - day(TT.`day`) + 1 day));";
	
	/**
	 * 更新临时订单和会议室订单的付款状态
	 * @param billId
	 */
	@Override
	public void updateOtherOrder(Integer billId){
		Session session = sessionFactory.getCurrentSession();
		Query query  = session.createSQLQuery(sqlUpdateTempOrderStatus);
		query.setParameter(0, billId);
		query.executeUpdate();
		query  = session.createSQLQuery(sqlUpdateMeetingOrderStatus);
		query.setParameter(0, billId);
		query.executeUpdate();
	}
	
}